package com.open.yujiakj.common.utils.office;

import com.open.yujiakj.common.exception.BizException;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: hl
 * @create: 2024-05-30 18:19
 **/
@Slf4j
public class ExcelFuUtil {


    @SneakyThrows
    public static Map<String, List<Map<String, Object>>> parseAllFromFile(File file, int sheetIndex) {
        Map<String, List<Map<String, Object>>> result = new HashMap<>();
        if (null == file || !file.exists() || !file.isFile()) {
            throw new BizException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getName().toLowerCase();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BizException(600, "请上传XLSX格式文件");
        }
        try (InputStream fis = new FileInputStream(file)) {
            parseXLSX(fis);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;

    }


    private static List<Map<String, Object>> parseXLSX(InputStream fis) {
        List<Map<String, Object>> datas = new ArrayList<>();
        ;
        try (XSSFWorkbook book = new XSSFWorkbook(fis);) {

            for (int sheetIndex = 0; sheetIndex < book.getNumberOfSheets(); sheetIndex++) {
//                if (sheetIndex != 4) {
//                    continue;
//                }
                XSSFSheet sheet = book.getSheetAt(sheetIndex);
                String sheetName = sheet.getSheetName().replace(" ", "");
                if (sheet.getLastRowNum() < 0) {
                    return datas;
                }

                for (int rowIndex = 1; rowIndex < sheet.getLastRowNum() + 1; rowIndex++) {
                    XSSFRow row = sheet.getRow(rowIndex);
                    if (row == null) {
                        continue;
                    }
                    int rowNum = row.getRowNum();
                    Map<String, Object> data = new HashMap<>();
                    for (int i = 0; i < row.getLastCellNum() + 1; i++) {
                        XSSFCell cell = row.getCell(i);
                        if (null == cell) {
                            continue;
                        }
                        int cellIndex = cell.getColumnIndex();
                        String columnName = getColumnName(cellIndex);
                        //   String columnLetter = CellReference.convertNumToColString(cellIndex); // 将数字转换为列字母
                        if (CellType.FORMULA.equals(cell.getCellType())) {
                            String formula = cell.getCellFormula()
                                    .replace(" ", "")
                                    .replace("$", "")
                                    .replace("'", "");
                            if (formula.contains("SUM")) {
                                formula = getSumToFormula(formula);
                            }
                            System.out.printf("%s,%s,%s,%s%s,%s \n", rowIndex + 1, cellIndex + 1, sheetName, columnName, rowIndex + 1, formula);
                            // log.info("表:{} 列：{}{} 行:{}   \n ", sheetName,cellIndex,columnName,rowIndex+1,formula);
                        }

                    }
                }
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return datas;
    }


    // 将列索引转换为字母名称的方法
    public static String getColumnName(int columnIndex) {
        StringBuilder columnName = new StringBuilder();
        while (columnIndex >= 0) {
            int letterIndex = (columnIndex % 26); // 获取当前位的字母索引（A=0, B=1, ..., Z=25）
            columnName.insert(0, (char) ('A' + letterIndex)); // 将数字转换为字母并插入到字符串开始处
            columnIndex = (columnIndex / 26) - 1; // 计算下一个更高位的索引（需要减1因为Excel的列是从1开始计数的）
        }
        return columnName.toString();
    }


    public static String getSumToFormula(String input) {
        //SUM(C39:AF39)
        String str = input.toUpperCase().replace("SUM(", "")
                .replace(")", "");
        String[] strings = str.split(":");
        if (strings.length != 2) {
            return input;
        }
        String letters1 = strings[0].replaceAll("[^a-zA-Z]", ""); // 提取字母
        Integer digits1 = Integer.valueOf(strings[0].replaceAll("[^0-9]", "")); // 提取数字
        String letters2 = strings[1].replaceAll("[^a-zA-Z]", ""); // 提取字母
        Integer digits2 = Integer.valueOf(strings[1].replaceAll("[^0-9]", "")); // 提取数字
        if (letters1.equals(letters2)) {
            //纵向加
            String strSum = letters1 + digits1;
            while (digits1 < digits2) {
                digits1++;
                strSum = strSum + "+" + letters1 + digits1;
            }
            return strSum;
        }
        if (digits1.equals(digits2)) {
            //横向加
            String strSum = letters2 + digits1;
            int startIndex = CellReference.convertColStringToIndex(letters1);
            int endIndex = CellReference.convertColStringToIndex(letters2);
            //倒序 防止E38把AE38  替换
            while (startIndex < endIndex){
                endIndex--;
                String columnName = CellReference.convertNumToColString(endIndex);
                strSum = strSum + "+" + columnName + digits1;
            }
            return strSum;

        }

        return input;

    }


    @SneakyThrows
    public static void main(String[] args) {

        Map<String, List<Map<String, Object>>> map = parseAllFromFile(
                new File("C:\\Users\\happy\\Documents\\亚东\\亚东2503\\PA66分摊比例2024.12.xlsx"),
                0);
        System.out.println(map);
    }
}
